package com.ruoyi.system.util;

import com.ruoyi.system.domain.Evaluating.ExcelData;
import com.ruoyi.system.domain.standard_relevant.StandardExcel;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Slf4j
public class ExcelReader {


    public  List<ExcelData> readExcelByColumnNames(String filePath, String[] columnNames, String[] rowNames) throws IOException {

        File excelFile = new File(filePath);
        FileInputStream fis = new FileInputStream(excelFile);
        Workbook workbook = new XSSFWorkbook(fis);
        Sheet sheet = workbook.getSheetAt(0);
        Row headerRow = sheet.getRow(0);

        // 找到所需列的索引
        Map<String, Integer> columnIndexMap = new HashMap<>();
        for (Cell cell : headerRow) {
            for (String columnName : columnNames) {
                if (cell.getStringCellValue().equals(columnName)) {
                    columnIndexMap.put(columnName, cell.getColumnIndex());
                }
            }
        }
        //打印这个columnIndexMap
        for(Map.Entry<String, Integer> entry : columnIndexMap.entrySet()) {
            log.info("{}: {}", entry.getKey(), entry.getValue());
        }

        // 筛选数据
        List<ExcelData> dataList = new ArrayList<>();
        for (Row row : sheet) {
            if (row.getRowNum() == 0) continue;
            String rowName = row.getCell(0).getStringCellValue(); // Assuming the first column contains row names
            if (Arrays.asList(rowNames).contains(rowName)) {
                double[] data = new double[columnNames.length];
                for (int i = 0; i < columnNames.length; i++) {
                    Integer colIndex = columnIndexMap.get(columnNames[i]); // 注意这里将int改为了Integer，以便能够处理null值
                    if (colIndex != null) { // 检查colIndex是否为null
                        data[i] = row.getCell(colIndex).getNumericCellValue();
                    } else {
                        // 处理colIndex为null的情况，这里我使用0作为默认值，但您可以根据需求修改
                        data[i] = 0.0;
                        // 或者您可以记录一个错误，跳过该单元格，或者执行其他操作
                        // System.err.println("No index found for column name: " + columnNames[i]);
                    }
                }
                dataList.add(new ExcelData(rowName, columnNames, data));
            }
        }
        workbook.close();
        return dataList;
    }

    public  List<ExcelData> convertToExcelDataList(List<StandardExcel> standardExcelList) {
        List<ExcelData> excelDataList = new ArrayList<>();

        for (StandardExcel standardExcel : standardExcelList) {
            String rowName = standardExcel.getStandardCode(); // 使用标准号作为行名称

            String[] columnNames = new String[]{
                    "A1", "A2", "A3", "B1", "B2", "B3"
            };

            double[] data = new double[]{
                    standardExcel.getA1(), standardExcel.getA2(), standardExcel.getA3(),
                    standardExcel.getB1(), standardExcel.getB2(), standardExcel.getB3()
            };

            ExcelData excelData = new ExcelData(rowName, columnNames, data);
            excelDataList.add(excelData);
        }

        return excelDataList;
    }
    public  List<ExcelData> convertToExcelDataList2(List<StandardExcel> standardExcelList) {
        List<ExcelData> excelDataList = new ArrayList<>();

        for (StandardExcel standardExcel : standardExcelList) {
            String rowName = standardExcel.getStandardCode(); // 使用标准号作为行名称

            String[] columnNames = new String[]{
                    "A11", "A12", "A13",
                    "A21", "A22", "A23",
                    "A31", "A32", "A33",
                    "A41", "A42", "A43",
                    "B11", "B12", "B13",
                    "B21", "B22", "B23",
                    "B31", "B32", "B33",
                    "B41", "B42", "B43",
                    "B51", "B52", "B53",
                    "C11"

            };

            double[] data = new double[]{
                    standardExcel.getA11(), standardExcel.getA12(), standardExcel.getA13(),
                    standardExcel.getA21(), standardExcel.getA22(), standardExcel.getA23(),
                    standardExcel.getA31(), standardExcel.getA32(), standardExcel.getA33(),
                    standardExcel.getA41(), standardExcel.getA42(), standardExcel.getA43(),
                    standardExcel.getB11(), standardExcel.getB12(), standardExcel.getB13(),
                    standardExcel.getB21(), standardExcel.getB22(), standardExcel.getB23(),
                    standardExcel.getB31(), standardExcel.getB32(), standardExcel.getB33(),
                    standardExcel.getB41(), standardExcel.getB42(), standardExcel.getB43(),
                    standardExcel.getB51(), standardExcel.getB52(), standardExcel.getB53(),
                    standardExcel.getC11()
            };

            ExcelData excelData = new ExcelData(rowName, columnNames, data);
            excelDataList.add(excelData);
        }

        return excelDataList;
    }
    public static String extractPathAfterProfile(String excelPath) {
        String regex = "/profile/(.+?\\.[^.]+$)";

        // 编译正则表达式
        Pattern pattern = Pattern.compile(regex);

        // 在目标字符串上执行匹配
        Matcher matcher = pattern.matcher(excelPath);

        // 查找匹配项
        if (matcher.find()) {
            // 提取匹配到的路径部分（不包括前面的 "/profile/"）
            return matcher.group(1);
        } else {
            // 可以选择返回null或者抛出异常
            return null; // 或者 throw new IllegalArgumentException("没有找到匹配的路径。");
        }
    }
//    public static void main(String[] args) throws IOException {
//
//
////        System.out.println(System.getProperty("user.dir"));
////        String pathName= System.getProperty("user.dir")+"/impact-back/src/main/java/com/ruoyi/system/util/fours.xlsx";
////        // 定义列名和行名
////        String[] columnNames = {"A11",	"A12"};
////        String[] rowNames = {"IEC 60050-617:2009", "IEC 61169-39:2009", "IEC 62341-6-1:2022"};
////        System.out.println("Hello World");
////
////        // 调用你的方法，但传递一个包装了FileInputStream的InputStream
////        List<ExcelData> excelDataList = readExcelByColumnNames(pathName, columnNames, rowNames);
////        // 你可以在这里处理excelDataList...
////        for (ExcelData data2 : excelDataList) {
////            System.out.println("Row: " + data2.getRowName() + ", 指标值: " + Arrays.toString(data2.getData())+ "ColumnNames: " + Arrays.toString(data2.getColumnNames()));
////        }
//        String excelPath = "http://localhost:8080/profile/upload/excel/2024/07/23/standard_excel_20240723154827A002.xlsx";
//
//        // 正则表达式，匹配从 "/profile/" 开始直到文件扩展名之前的所有内容
//        String regex = "/profile/(.+?\\.[^.]+$)";
//
//        // 编译正则表达式
//        Pattern pattern = Pattern.compile(regex);
//
//        // 在目标字符串上执行匹配
//        Matcher matcher = pattern.matcher(excelPath);
//
//        // 查找匹配项
//        if (matcher.find()) {
//            // 提取匹配到的路径部分（不包括前面的 "/profile/"）
//            String extractedPath = matcher.group(1);
//            System.out.println(extractedPath); // 输出: upload/excel/2024/07/23/standard_excel_20240723154827A002.xlsx
//        } else {
//            System.out.println("没有找到匹配的路径。");
//        };
//    }
}
